import logging
import math
import time
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

ts_task_router = APIRouter(prefix="/tsTask", tags=["课题组管理"], dependencies=[Depends(oauth2_scheme)])


class ResearchGroupSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftaskname: Optional[str] = Field("", description="任务名称(支持前缀匹配)")
    fteamno: Optional[str] = Field("", description="团队编号")


@ts_task_router.get("/list", summary="")
def list_ts_task(search: ResearchGroupSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from m_ts_task where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.ftaskname != "":
        sql = sql + " and ftaskname like concat('%', :ftaskname ,'%')"
        param["ftaskname"] = search.ftaskname
    if search.fteamno != "":
        sql = sql + " and fteamno = :fteamno "
        param["fteamno"] = search.fteamno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select ftaskno,fteamno,ftaskname,fsite_no,fperson,fmemo,fstate,fupddate,foperator"
    sql = sql + " from m_ts_task where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.ftaskname != "":
        sql = sql + " and ftaskname like concat('%', :ftaskname ,'%')"
        param["ftaskname"] = search.ftaskname
    if search.fteamno != "":
        sql = sql + " and fteamno = :fteamno "
        param["fteamno"] = search.fteamno
    if search.order == "desc":
        sql = sql + " order by  ftaskno desc "
    else:
        sql = sql + " order by  ftaskno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ftaskno'] = data[key][0]
        d1['fteamno'] = data[key][1]
        d1['ftaskname'] = data[key][2]
        d1['fsiteno'] = data[key][3]
        d1['fperson'] = data[key][4]
        d1['fmemo'] = data[key][5]
        d1['fstate'] = data[key][6]
        d1['fupddate'] = data[key][7]
        d1['foperator'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class ResearchGroupCreate(BaseModel):
    """课题组资料新增请求参数"""
    ftaskno: str = Field(..., description="课题组编号")
    fteamno: str = Field(..., description="团队编号")
    ftaskname: str = Field(..., description="任务名称")
    fsiteNo: str = Field(..., description="站点编号")
    fperson: str = Field(..., description="负责人ID")
    fstate: int = Field(..., description="状态编码")
    fmemo: Optional[str] = Field("", description="备注说明")


@ts_task_router.post("/create", summary="")
def create_ts_task(create: ResearchGroupCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select ftaskno from m_ts_task where ftaskno= :ftaskno and fsite_No= :fsiteNo "

            data = db.execute(text(sql), {
                "ftaskno": create.ftaskno,
                "fsiteNo": create.fsiteNo,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="课题组编号已存在")
            else:
                sql = "insert into m_ts_task(ftaskno,fteamno,ftaskname,fsite_no,fperson,fmemo,fstate,fupddate,foperator)"
                sql = sql + " values( :ftaskno , :fteamno , :ftaskname , :fsiteNo , :fperson , :fmemo , :fstate ,now(), :userid );"
                db.execute(text(sql), {
                    "ftaskno": create.ftaskno,
                    "fteamno": create.fteamno,
                    "ftaskname": create.ftaskname,
                    "fsiteNo": create.fsiteNo,
                    "fperson": create.fperson,
                    "fmemo": create.fmemo,
                    "fstate": create.fstate,
                    "userid": user_id,
                })
                # 返回
                current_datetime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                d1 = {}
                d1['ftaskno'] = create.ftaskno
                d1['fteamno'] = create.fteamno
                d1['ftaskname'] = create.ftaskname
                d1['fsiteno'] = create.fsiteNo
                d1['fperson'] = create.fperson
                d1['fmemo'] = create.fmemo
                d1['fstate'] = create.fstate
                d1['fupddate'] = current_datetime
                d1['foperator'] = user_id
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@ts_task_router.post("/update", summary="")
def update_ts_task(update: ResearchGroupCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update m_ts_task"
            sql = sql + " set ftaskname= :ftaskname ,fperson= :fperson "
            sql = sql + ",fmemo= :fmemo ,fstate= :fstate ,fteamno= :fteamno "
            sql = sql + " ,fupddate=now(),foperator= :userid "
            sql = sql + " where fteamno= :ftaskno  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "ftaskname": update.ftaskname,
                "fperson": update.fperson,
                "fmemo": update.fmemo,
                "fstate": update.fstate,
                "fteamno": update.fteamno,
                "userid": user_id,
                "ftaskno": update.ftaskno,
                "fsiteNo": update.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@ts_task_router.post("/delete/{ftaskno}", summary="")
def delete_ts_task(ftaskno: str = Path(description="课题组编号"), db: Session = Depends(get_db)):
    try:
        param = {"ftaskno": ftaskno}
        with db.begin():
            sql = "select count(1) from meeting where ftaskno= :ftaskno "
            data = db.execute(text(sql), param).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="编号已存在，不能新增")
            else:
                sql = "delete from m_ts_task where ftaskno= :ftaskno "
                db.execute(text(sql), param)
                sql = "delete from m_ts_taskpoint where ftaskno= :ftaskno "
                db.execute(text(sql), param)
                sql = "delete from m_ts_taskuser where ftaskno= :ftaskno "
                db.execute(text(sql), param)
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class BalanceSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fteamno: Optional[str] = Field("", description="团队编号")
    ftaskno: Optional[str] = Field("", description="任务编号")


@ts_task_router.get("/listMoney", summary="余额查询查询接口")
def query_money(search: BalanceSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from m_tc_dailyaccount a,m_ts_task b,m_ts_team c"
    sql = sql + " where 1=1"
    sql = sql + " and a.ftaskno=b.ftaskno "
    sql = sql + " and b.fteamno=c.fteamno"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param['fsiteNo'] = search.fsiteNo
    if search.ftaskno != "":
        sql = sql + " and b.ftaskno= :ftaskno "
        param['ftaskno'] = search.ftaskno
    if search.fteamno != "":
        sql = sql + " and c.fteamno = :fteamno "
        param['fteamno'] = search.fteamno
    sql = sql + " group by a.fsite_no,b.ftaskno,b.ftaskname,c.fteamno,c.fteamname"
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fsite_no,b.ftaskno,b.ftaskname,c.fteamno,c.fteamname,sum(a.fchargeamt) money"
    sql = sql + " from m_tc_dailyaccount a,m_ts_task b,m_ts_team c"
    sql = sql + " where 1=1"
    sql = sql + " and a.ftaskno=b.ftaskno "
    sql = sql + " and b.fteamno=c.fteamno"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param['fsiteNo'] = search.fsiteNo
    if search.ftaskno != "":
        sql = sql + " and b.ftaskno= :ftaskno "
        param['ftaskno'] = search.ftaskno
    if search.fteamno != "":
        sql = sql + " and c.fteamno = :fteamno "
        param['fteamno'] = search.fteamno
    sql = sql + " group by a.fsite_no,b.ftaskno,b.ftaskname,c.fteamno,c.fteamname"
    if search.order == "desc":
        sql = sql + " order by a.fsite_no,b.ftaskno desc "
    else:
        sql = sql + " order by a.fsite_no,b.ftaskno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteNo'] = data[key][0]
        d1['ftaskno'] = data[key][1]
        d1['ftaskname'] = data[key][2]
        d1['fteamno'] = data[key][3]
        d1['fteamname'] = data[key][4]
        d1['money'] = data[key][5]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
